﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using NPOI.XSSF.UserModel;
using NPOI.POIFS.FileSystem;

namespace zhongchi_xinchouBiz.Utility
{
    static class RowCellHelper
    {
        public static string GetString(this ICell cell)
        {
            string result = null;
            if (cell != null) { result = cell.ToString(); }
            return result;
        }
        public static string GetString(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell.GetString();
        }
        public static decimal GetDecimal(this ICell cell)
        {
            var result = decimal.Zero;
            var s = cell.GetString();
            decimal.TryParse(s, out result);
            return result;
        }
        public static decimal GetDecimal(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell.GetDecimal();
        }
        public static decimal? GetDecimalNullable(this ICell cell)
        {
            var result = decimal.Zero;
            var s = cell.GetString();
            if (!decimal.TryParse(s, out result)) { return null; }
            return result;
        }
        public static decimal? GetDecimalNullable(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell.GetDecimalNullable();
        }
        public static DateTime GetDateTime(this ICell cell)
        {
            var result = DateTime.MinValue;
            var s = cell.GetString();
            DateTime.TryParse(s, out result);
            return result;
        }
        public static DateTime GetDateTime(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell.GetDateTime();
        }
        public static int GetInt(this ICell cell)
        {
            var result = 0;
            var s = cell.GetString();
            int.TryParse(s, out result);
            return result;
        }
        public static int GetInt(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell.GetInt();
        }
    }
    public class ExcelHelp
    {
        public static void excel(List<Dictionary<string, object>> list, string fileName, string[] strCol, string[] Col)
        {
            var isOldExcel = Path.GetExtension(fileName).Equals(".xls", StringComparison.OrdinalIgnoreCase);
            IWorkbook workbook = null;
            if (isOldExcel)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            try
            {
                ISheet sheet = workbook.CreateSheet("Sheet1");
                sheet.FitToPage = false;

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //表头
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (var col in strCol)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(col);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (var row in list)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (var col in Col)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.CellStyle = cellStyle;
                        if (row[col] is decimal)
                        {
                            cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                            cell.SetCellValue(Convert.ToDouble(row[col]));
                        }
                        else if (row[col] is DateTime)
                        {
                            cell.SetCellValue(((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else if (row[col] == null || row[col] is DBNull)
                        {
                            cell.SetCellValue("");
                        }
                        else
                        {
                            cell.SetCellValue(row[col].ToString());
                        }
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                FileStream file = new FileStream(fileName, FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
            }
            catch
            {
                throw;
            }
            finally { workbook = null; }
        }
        public static void ToExcel<T>(List<T> list, string fileName, string[] columns, Func<T, string[]> convertValueArray)
        {
            var isOldExcel = Path.GetExtension(fileName).Equals(".xls", StringComparison.OrdinalIgnoreCase);
            IWorkbook workbook = null;
            if (isOldExcel)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            try
            {
                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //表头
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (var column in columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(column);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (var item in list)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    var rowValues = convertValueArray(item);

                    foreach (var value in rowValues)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(value);
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                }
            }
            finally
            {
                workbook = null;
            }
        }
        public static Stream ToExcel<T>(List<T> list,string[] columns, Func<T, string[]> convertValueArray)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {
                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //表头
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (var column in columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(column);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (var item in list)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    var rowValues = convertValueArray(item);

                    foreach (var value in rowValues)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(value);
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                MemoryStream file = new MemoryStream();
                workbook.Write(file);
                file.Position = 0;
                return file;
            }
            finally
            {
                workbook = null;
            }
        }

        public static DataTable Load(string fileName)
        {
            var isOldExcel = Path.GetExtension(fileName).Equals(".xls", StringComparison.OrdinalIgnoreCase);
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable table = new DataTable(Path.GetFileName(fileName));
            using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                if (isOldExcel)
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }
                sheet = workbook.GetSheetAt(0);

                IRow row = null; bool isBreak;
                var startRowNum = sheet.FirstRowNum;
                var endRowNum = sheet.LastRowNum;
                {
                    row = sheet.GetRow(startRowNum);
                    var stringType = string.Empty.GetType();
                    for (int cellIndex = row.FirstCellNum, cellMaxIndex = row.LastCellNum; cellIndex <= cellMaxIndex; cellIndex++)
                    {
                        table.Columns.Add("column" + cellIndex.ToString());
                    }
                }
                startRowNum += 1;
                try
                {
                    for (int rowIndex = startRowNum; rowIndex <= endRowNum; rowIndex++)
                    {
                        row = sheet.GetRow(rowIndex);
                        if (row == null) { continue; }
                        isBreak = false;
                        var r = table.NewRow();
                        for (int cellIndex = row.FirstCellNum, cellMaxIndex = row.LastCellNum; cellIndex <= cellMaxIndex; cellIndex++)
                        {
                            if (isBreak = !string.IsNullOrWhiteSpace(row.GetCell(cellIndex).GetString()))
                            {
                                for (int readIndex = row.FirstCellNum, readMaxIndex = row.LastCellNum; readIndex <= readMaxIndex; readIndex++)
                                {
                                    r["column" + readIndex.ToString()] = row.GetCell(readIndex).GetString();
                                }
                                break;
                            }
                        }
                        if (isBreak)
                        {
                            table.Rows.Add(r);
                        }
                    }
                }
                catch (Exception ex)
                {
                    if (row != null) { throw new NotSupportedException(string.Format("在读取第{0}行时发生错误[{1}]！", row.RowNum, ex.Message), ex); }
                    else { throw; }
                }
            }
            sheet = null;
            workbook = null;
            return table;
        }
        public static DataTable LoadByRowNum(string fileName, int rownum)
        {
            var isOldExcel = Path.GetExtension(fileName).Equals(".xls", StringComparison.OrdinalIgnoreCase);
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable table = new DataTable(Path.GetFileName(fileName));
            using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                if (isOldExcel)
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }
                sheet = workbook.GetSheetAt(0);

                IRow row = null; bool isBreak;
                var startRowNum = rownum;
                var endRowNum = sheet.LastRowNum;
                {
                    row = sheet.GetRow(startRowNum);
                    var stringType = string.Empty.GetType();
                    for (int cellIndex = row.FirstCellNum, cellMaxIndex = row.LastCellNum; cellIndex <= cellMaxIndex; cellIndex++)
                    {
                        table.Columns.Add("column" + cellIndex.ToString());
                    }
                }
                startRowNum += 1;
                try
                {
                    for (int rowIndex = startRowNum; rowIndex <= endRowNum; rowIndex++)
                    {
                        row = sheet.GetRow(rowIndex);
                        if (row == null) { continue; }
                        isBreak = false;
                        var r = table.NewRow();
                        for (int cellIndex = row.FirstCellNum, cellMaxIndex = row.LastCellNum; cellIndex <= cellMaxIndex; cellIndex++)
                        {
                            if (isBreak = !string.IsNullOrWhiteSpace(row.GetCell(cellIndex).GetString()))
                            {
                                for (int readIndex = row.FirstCellNum, readMaxIndex = row.LastCellNum; readIndex <= readMaxIndex; readIndex++)
                                {
                                    r["column" + readIndex.ToString()] = row.GetCell(readIndex).GetString();
                                }
                                break;
                            }
                        }
                        if (isBreak)
                        {
                            table.Rows.Add(r);
                        }
                    }
                }
                catch (Exception ex)
                {
                    if (row != null) { throw new NotSupportedException(string.Format("在读取第{0}行时发生错误[{1}]！", row.RowNum, ex.Message), ex); }
                    else { throw; }
                }
            }
            sheet = null;
            workbook = null;
            return table;
        }

        /// <summary>
        /// 根据不同模板  生成 excel    
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="createpath"></param>
        /// <param name="list"></param>
        public static void ToExcel(string fileName, string createpath, List<Dictionary<string, object>> list)
        {
            File.Copy(fileName, createpath);
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流
            POIFSFileSystem ps = new POIFSFileSystem(fs);//需using NPOI.POIFS.FileSystem;
            IWorkbook workbook = new HSSFWorkbook(ps);
            ISheet sheet = workbook.GetSheetAt(0);//获取工作表


            ICellStyle cellStyle = workbook.CreateCellStyle();

            //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
            //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)FontBoldWeight.Normal;
            cellStyle.SetFont(cellfont);

            IRow row = sheet.GetRow(1); //得到表头
            var cells = row.Cells.Select(c => c.RichStringCellValue.ToString()).ToList();//表头列
            FileStream fout = new FileStream(createpath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
            foreach (var item in list)
            {
                int y = 1;
                row = sheet.CreateRow((sheet.LastRowNum + y));//创建行
                for (int i = 0; i < cells.Count; i++)
                {
                    ICell cell1 = row.CreateCell(i);//创建列
                    cell1.CellStyle = cellStyle;
                    if (item.ContainsKey(cells[i]) && item[cells[i]] != null)//判断是否包含此键
                    {
                        cell1.SetCellValue(item[cells[i]].ToString());//赋值
                    }
                    else
                    {
                        cell1.SetCellValue("");//赋值
                    }
                }
                y++;
            }
            fout.Flush();
            workbook.Write(fout);//写入文件
            workbook = null;
            fout.Close();
        }

        public static MemoryStream ToExcel(string fileName, List<Dictionary<string, object>> list)
        {
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流
            //POIFSFileSystem ps = new POIFSFileSystem(fs);//需using NPOI.POIFS.FileSystem;
            IWorkbook workbook = new XSSFWorkbook(fs);
          //  IWorkbook workbook = new HSSFWorkbook(ps);
            ISheet sheet = workbook.GetSheetAt(0);//获取工作表
            IRow row = sheet.GetRow(2); //得到表头
            var cells = row.Cells.Select(c => c.RichStringCellValue.ToString()).ToList();//表头列
            MemoryStream fout = new MemoryStream();
            foreach (var item in list)
            {
         
                int y = 1;
                row = sheet.CreateRow((sheet.LastRowNum + y));//创建行
                for (int i = 0; i < cells.Count; i++)
                {
                    ICell cell1 = row.CreateCell(i);//创建列
                    if (item.ContainsKey(cells[i]) && item[cells[i]] != null)//判断是否包含此键
                    {
                        cell1.SetCellValue(item[cells[i]].ToString());//赋值
                    }
                    else
                    {
                        cell1.SetCellValue("");//赋值
                    }
                }
                y++;
            }
            workbook.Write(fout);//写入文件
            fout.Flush();
            workbook = null;
            return fout;
        }
    }
}